package com.hethongviet.job.helpers.database;

import java.util.ArrayList;
import java.util.List;

import com.google.android.maps.GeoPoint;
import com.hethongviet.job.AppMain;
import com.hethongviet.job.helpers.ServiceHelper;
import com.hethongviet.job.models.FileGeneral;
import com.hethongviet.job.models.FileImage;
import com.hethongviet.job.models.Task;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class TaskSQLite
{
	public static final String TABLE_NAME = "task";
	public static final String COLUMN_ID_DB = "idDB";

	public static final String COLUMN_ID = "id";
	public static final String COLUMN_CODE = "code";
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_WORKER = "worker";

	public static final String COLUMN_PROJECT_ID = "project_id";
	public static final String COLUMN_PROJECT_CODE = "project_code";
	public static final String COLUMN_PROJECT_NAME = "project_name";
	public static final String COLUMN_DESCRIPTION = "description";
	public static final String COLUMN_DATE_CREATE = "date_create";

	public static final String COLUMN_DATE_ACTUAL_START = "date_actual_start";
	public static final String COLUMN_DATE_ACTUAL_FINISH = "date_actual_finish";
	public static final String COLUMN_DATE_REQUIRE_START = "date_require_start";
	public static final String COLUMN_DATE_REQUIRE_FINISH = "date_require_finish";

	public static final String COLUMN_NOTE = "note";
	public static final String COLUMN_IMPORTANCE = "level_importance";
	public static final String COLUMN_ATTACHMENT = "attachment";
	public static final String COLUMN_STATUS = "status";

	public static final String COLUMN_IMAGE_START = "image_start";
	public static final String COLUMN_IMAGE_FINISH = "image_finish";
	public static final String COLUMN_IMAGE_START_PATH = "image_start_path";
	public static final String COLUMN_IMAGE_FINISH_PATH = "image_finish_path";
	public static final String COLUMN_IS_SYNC_START = "is_sync";
	public static final String COLUMN_IS_SYNC_FINISH = "is_sync_finish";

	public static final String COLUMN_GPS_START_LATITUDE = "latitude_start";
	public static final String COLUMN_GPS_START_LONGITUDE = "longtitude_start";
	public static final String COLUMN_GPS_FINISH_LATITUDE = "latitude_finish";
	public static final String COLUMN_GPS_FINISH_LONGITUDE = "longitude_finish";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;
	private String[] _allColumns = { COLUMN_ID_DB, COLUMN_ATTACHMENT,
			COLUMN_CODE, COLUMN_DATE_ACTUAL_FINISH, COLUMN_DATE_ACTUAL_START,
			COLUMN_DATE_CREATE, COLUMN_DATE_REQUIRE_FINISH,
			COLUMN_DATE_REQUIRE_START, COLUMN_DESCRIPTION,
			COLUMN_GPS_FINISH_LATITUDE, COLUMN_GPS_FINISH_LONGITUDE,
			COLUMN_GPS_START_LATITUDE, COLUMN_GPS_START_LONGITUDE, COLUMN_ID,
			COLUMN_IMAGE_FINISH, COLUMN_IMAGE_FINISH_PATH, COLUMN_IMAGE_START, COLUMN_IMAGE_START_PATH, COLUMN_IS_SYNC_START, COLUMN_IS_SYNC_FINISH, COLUMN_IMPORTANCE,
			COLUMN_NAME, COLUMN_NOTE, COLUMN_PROJECT_ID, COLUMN_PROJECT_CODE,
			COLUMN_PROJECT_NAME, COLUMN_STATUS, COLUMN_WORKER };

	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
			+ COLUMN_ATTACHMENT + " text, " + COLUMN_CODE + " text, "
			+ COLUMN_DATE_ACTUAL_FINISH + " text, " + COLUMN_DATE_ACTUAL_START
			+ " text, "

			+ COLUMN_DATE_CREATE + " text, " + COLUMN_DATE_REQUIRE_FINISH
			+ " text, " + COLUMN_DATE_REQUIRE_START + " text, "
			+ COLUMN_DESCRIPTION + " text, "

			+ COLUMN_GPS_FINISH_LATITUDE + " text, "
			+ COLUMN_GPS_FINISH_LONGITUDE + " text, "
			+ COLUMN_GPS_START_LATITUDE + " text, "
			+ COLUMN_GPS_START_LONGITUDE + " text, "

			+ COLUMN_ID + " text, " + COLUMN_IMAGE_FINISH + " text, "
			+ COLUMN_IMAGE_FINISH_PATH + " text, " + COLUMN_IMAGE_START
			+ " text, " + COLUMN_IMAGE_START_PATH + " text, " + COLUMN_IS_SYNC_START
			+ " integer, " + COLUMN_IS_SYNC_FINISH
			+ " integer, " + COLUMN_IMPORTANCE + " text, "

			+ COLUMN_NAME + " text, " + COLUMN_NOTE + " text, "
			+ COLUMN_PROJECT_ID + " text, " + COLUMN_PROJECT_CODE + " text, "
			+ COLUMN_PROJECT_NAME + " text, "

			+ COLUMN_STATUS + " integer, " + COLUMN_WORKER + " text";

	public TaskSQLite(Context context)
	{
		_dbHelper = new MySQLiteHelper(context, null, null);
	}

	public void open() throws SQLException
	{
		_database = _dbHelper.getWritableDatabase();
	}

	public void close()
	{
		_dbHelper.close();
	}

	private boolean dataExist(Task task, Cursor cursor)
	{
		if (cursor == null)
		{
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		{
			long idDB = cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB));
			if (idDB == task.getIdDB())
				return true;
		}
		return false;
	}

	/**
	 * @param project
	 * @return row id -1: insert fail
	 */
	public long insert(Task task)
	{
		open();

		AppMain.debug("insert -- " + task.getName());
		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID_DB + " = " + task.getIdDB(), null);
		if (!dataExist(task, cursor))
		{
			AppMain.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery(
					"INSERT INTO "
							+ TABLE_NAME
							+ "("
							+ COLUMN_ATTACHMENT
							+ ", "
							+ COLUMN_CODE
							+ ", "
							+ COLUMN_DATE_ACTUAL_FINISH
							+ ", "
							+ COLUMN_DATE_ACTUAL_START
							+ ", "

							+ COLUMN_DATE_CREATE
							+ ", "
							+ COLUMN_DATE_REQUIRE_FINISH
							+ ", "
							+ COLUMN_DATE_REQUIRE_START
							+ ", "
							+ COLUMN_DESCRIPTION
							+ ", "

							+ COLUMN_GPS_FINISH_LATITUDE
							+ ", "
							+ COLUMN_GPS_FINISH_LONGITUDE
							+ ", "
							+ COLUMN_GPS_START_LATITUDE
							+ ", "
							+ COLUMN_GPS_START_LONGITUDE
							+ ", "

							+ COLUMN_ID
							+ ", "
							+ COLUMN_IMAGE_FINISH
							+ ", "
							+ COLUMN_IMAGE_FINISH_PATH
							+ ", "
							+ COLUMN_IMAGE_START
							+ ", "
							+ COLUMN_IMAGE_START_PATH
							+ ", "
							+ COLUMN_IS_SYNC_START
							+ ", "
							+ COLUMN_IS_SYNC_FINISH
							+ ", "
							+ COLUMN_IMPORTANCE
							+ ", "

							+ COLUMN_NAME
							+ ", "
							+ COLUMN_NOTE
							+ ", "
							+ COLUMN_PROJECT_ID
							+ ", "
							+ COLUMN_PROJECT_CODE
							+ ", "
							+ COLUMN_PROJECT_NAME
							+ ", "

							+ COLUMN_STATUS
							+ ", "
							+ COLUMN_WORKER

							+ ") values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",

					new String[] {
							task.getFileAttachment().getName(),
							task.getCode(),
							task.getDateActualFinish(),
							task.getDateActualStart(),
							task.getDateCreated(),
							task.getDateRequireFinish(),
							task.getDateRequireStart(),
							task.getDescription(),
							""
									+ ((task.getWorkerGPSGeoPointFinish() == null) ? ServiceHelper.LOCATION_ATTR_NONE
											: task.getWorkerGPSGeoPointFinish().getLatitudeE6()),
							""
									+ ((task.getWorkerGPSGeoPointFinish() == null) ? ServiceHelper.LOCATION_ATTR_NONE
											: task.getWorkerGPSGeoPointFinish().getLongitudeE6()),
							""
									+ ((task.getWorkerGPSGeoPointStart() == null) ? ServiceHelper.LOCATION_ATTR_NONE
											: task.getWorkerGPSGeoPointStart().getLatitudeE6()),
							""
									+ ((task.getWorkerGPSGeoPointStart() == null) ? ServiceHelper.LOCATION_ATTR_NONE
											: task.getWorkerGPSGeoPointStart().getLongitudeE6()),
							task.getId(),

							((task.getImageFileFinish() == null) ? ""
									: getStringValue(task.getImageFileFinish().getName())),

							((task.getImageFileFinish() == null) ? ""
									: getStringValue(task.getImageFileFinish().getPath())),

							((task.getImageFileStart() == null) ? ""
									: getStringValue(task.getImageFileStart().getName())),

							((task.getImageFileStart() == null) ? ""
									: getStringValue(task.getImageFileStart().getPath())),

									(task.isSyncDataStart() ? "1" : "0"),
									(task.isSyncDataFinish() ? "1" : "0"),

							task.getImportantLevel(), task.getName(),
							task.getNote(), task.getProjectId(),
							task.getProjectCode(), task.getProjectName(),
							"" + task.getStatus(), task.getWorker()

					});

			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast())
				return cursorInsert.getLong(cursorInsert.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		}
		else
		{
			AppMain.debug("insert 1111111111111 update");
			/* Update to database */
			Cursor cursorUpdate = _database.rawQuery(
					"UPDATE " + TABLE_NAME + " SET " + COLUMN_ATTACHMENT
							+ " = ?, " + COLUMN_CODE + " = ?, "
							+ COLUMN_DATE_ACTUAL_FINISH + " = ?, "
							+ COLUMN_DATE_ACTUAL_START + " = ?, "

							+ COLUMN_DATE_CREATE + " = ?, "
							+ COLUMN_DATE_REQUIRE_FINISH + " = ?, "
							+ COLUMN_DATE_REQUIRE_START + " = ?, "
							+ COLUMN_DESCRIPTION + " = ?, "

							+ COLUMN_GPS_FINISH_LATITUDE + " = ?, "
							+ COLUMN_GPS_FINISH_LONGITUDE + " = ?, "
							+ COLUMN_GPS_START_LATITUDE + " = ?, "
							+ COLUMN_GPS_START_LONGITUDE + " = ?, "

							+ COLUMN_ID + " = ?, " + COLUMN_IMAGE_FINISH
							+ " = ?, " + COLUMN_IMAGE_FINISH_PATH + " = ?, "
							+ COLUMN_IMAGE_START + " = ?, "
							+ COLUMN_IMAGE_START_PATH + " = ?, "
							+ COLUMN_IS_SYNC_START + " = ?, " + COLUMN_IS_SYNC_FINISH + " = ?, " + COLUMN_IMPORTANCE
							+ " = ?, "

							+ COLUMN_NAME + " = ?, " + COLUMN_NOTE + " = ?, "
							+ COLUMN_PROJECT_ID + " = ?, "
							+ COLUMN_PROJECT_CODE + " = ?, "
							+ COLUMN_PROJECT_NAME + " = ?, "

							+ COLUMN_STATUS + " = ?, " + COLUMN_WORKER
							+ " = ?  WHERE " + COLUMN_ID_DB + " = "
							+ task.getIdDB(),

					new String[] {
							task.getFileAttachment().getName(),
							task.getCode(),
							task.getDateActualFinish(),
							task.getDateActualStart(),
							task.getDateCreated(),
							task.getDateRequireFinish(),
							task.getDateRequireStart(),
							task.getDescription(),
							""
									+ ((task.getWorkerGPSGeoPointFinish() == null) ? ServiceHelper.LOCATION_ATTR_NONE
											: task.getWorkerGPSGeoPointFinish().getLatitudeE6()),
							""
									+ ((task.getWorkerGPSGeoPointFinish() == null) ? ServiceHelper.LOCATION_ATTR_NONE
											: task.getWorkerGPSGeoPointFinish().getLongitudeE6()),
							""
									+ ((task.getWorkerGPSGeoPointStart() == null) ? ServiceHelper.LOCATION_ATTR_NONE
											: task.getWorkerGPSGeoPointStart().getLatitudeE6()),
							""
									+ ((task.getWorkerGPSGeoPointStart() == null) ? ServiceHelper.LOCATION_ATTR_NONE
											: task.getWorkerGPSGeoPointStart().getLongitudeE6()),
							task.getId(),

							((task.getImageFileFinish() == null) ? ""
									: getStringValue(task.getImageFileFinish().getName())),

							((task.getImageFileFinish() == null) ? ""
									: getStringValue(task.getImageFileFinish().getPath())),

							((task.getImageFileStart() == null) ? ""
									: getStringValue(task.getImageFileStart().getName())),

							((task.getImageFileStart() == null) ? ""
									: getStringValue(task.getImageFileStart().getPath())),

									(task.isSyncDataStart() ? "1" : "0"),
									(task.isSyncDataFinish() ? "1" : "0"),
							task.getImportantLevel(), task.getName(),
							task.getNote(), task.getProjectId(),
							task.getProjectCode(), task.getProjectName(),
							"" + task.getStatus(), task.getWorker()

					});

			cursorUpdate.moveToFirst();
			if (!cursorUpdate.isAfterLast())
				return cursorUpdate.getLong(cursorUpdate.getColumnIndex(COLUMN_ID_DB));

			cursorUpdate.close();
		}
		cursor.close();
		close();
		return -1;
	}

	private String getStringValue(String string)
	{
		return (string == null)? "" : string;
	}
	
	public void update(Task task)
	{
		open();

		/* Update to database */

		ContentValues contentValues = new ContentValues();
		contentValues.put(COLUMN_DATE_ACTUAL_FINISH, task.getDateActualFinish());
		contentValues.put(COLUMN_DATE_ACTUAL_START, task.getDateActualStart());
		if (task.getImageFileFinish() != null)
		{
			contentValues.put(COLUMN_IMAGE_FINISH,
					task.getImageFileFinish().getName());
			contentValues.put(COLUMN_IMAGE_FINISH_PATH,
					task.getImageFileFinish().getPath());
		}
		if (task.getImageFileStart() != null)
		{
			contentValues.put(COLUMN_IMAGE_START,
					task.getImageFileStart().getName());
			contentValues.put(COLUMN_IMAGE_START_PATH,
					task.getImageFileStart().getPath());
		}
		contentValues.put(COLUMN_IS_SYNC_START, task.isSyncDataStart());
		contentValues.put(COLUMN_IS_SYNC_FINISH, task.isSyncDataFinish());
		contentValues.put(COLUMN_STATUS, task.getStatus());

		_database.update(TABLE_NAME, contentValues,
				COLUMN_ID + " = \'" + task.getId() + "\'", null);

		close();
	}

	public void deleteAll()
	{
		open();

		System.out.println("project deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null);
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1", null);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public List<Task> getAll()
	{
		open();

		List<Task> listTasks = new ArrayList<Task>();

		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			Task task = cursorToTask(cursor);
			listTasks.add(task);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return listTasks;
	}

	private Task cursorToTask(Cursor cursor)
	{
		Task task = new Task();
		task.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));

		task.setFileAttachment(new FileGeneral(cursor.getString(cursor.getColumnIndex(COLUMN_ATTACHMENT)), null, null));
		task.setCode(cursor.getString(cursor.getColumnIndex(COLUMN_CODE)));
		task.setDateActualFinish(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_ACTUAL_FINISH)));
		task.setDateActualStart(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_ACTUAL_START)));

		task.setDateCreated(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_CREATE)));
		task.setDateRequireFinish(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_REQUIRE_FINISH)));
		task.setDateRequireStart(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_REQUIRE_START)));
		task.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_DESCRIPTION)));

		String gl1 = cursor.getString(cursor.getColumnIndex(COLUMN_GPS_FINISH_LATITUDE));
		String glo1 = cursor.getString(cursor.getColumnIndex(COLUMN_GPS_FINISH_LONGITUDE));
		AppMain.debug("start = " + gl1 + "," + glo1);
		try
		{
			task.setWorkerGPSGeoPointFinish(new GeoPoint(Integer.parseInt(gl1),
					Integer.parseInt(glo1)));
		} catch (Exception e)
		{
			e.printStackTrace();
		}

		String gl2 = cursor.getString(cursor.getColumnIndex(COLUMN_GPS_START_LATITUDE));
		String glo2 = cursor.getString(cursor.getColumnIndex(COLUMN_GPS_START_LONGITUDE));
		AppMain.debug("finish = " + gl2 + "," + glo2);

		try
		{
			task.setWorkerGPSGeoPointStart(new GeoPoint(Integer.parseInt(gl2),
					Integer.parseInt(glo2)));
		} catch (Exception e)
		{
			e.printStackTrace();
		}

		task.setId(cursor.getString(cursor.getColumnIndex(COLUMN_ID)));
		
		task.setImageFileFinish(new FileImage(
				null,
				cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_FINISH)),
				null,
				cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_FINISH_PATH))));

		task.setImageFileStart(new FileImage(
				null,
				cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_START)),
				null,
				cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_START_PATH))));
		
		task.setImportantLevel(cursor.getString(cursor.getColumnIndex(COLUMN_IMPORTANCE)));

		task.setSyncDataStart(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_SYNC_START)) == 1);
		task.setSyncDataFinish(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_SYNC_FINISH)) == 1);

		task.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
		task.setNote(cursor.getString(cursor.getColumnIndex(COLUMN_NOTE)));
		task.setProjectId(cursor.getString(cursor.getColumnIndex(COLUMN_PROJECT_ID)));
		task.setProjectCode(cursor.getString(cursor.getColumnIndex(COLUMN_PROJECT_CODE)));
		task.setProjectName(cursor.getString(cursor.getColumnIndex(COLUMN_PROJECT_NAME)));

		try
		{
			task.setStatus(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_STATUS))));
		} catch (Exception e)
		{
			e.printStackTrace();
			task.setStatus(0);
		}

		task.setWorker(cursor.getString(cursor.getColumnIndex(COLUMN_WORKER)));

		return task;
	}

	public final boolean isTableExist()
	{
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery(
				"select DISTINCT tbl_name from sqlite_master where tbl_name = '"
						+ TABLE_NAME + "'", null);
		if (cursor != null && cursor.getCount() > 0)
			isExist = true;

		cursor.close();
		close();

		return isExist;
	}
}
